CS 474 Lab 3 - Based on a lab by Chris Mayfield.

In this lab, you will take a design that has been created (based on your work in the lab on the Drivers database) and implement that as a series of tables. You will also take that implementation and insert data elements into the tables in order to prepare them for queries.

As you recall, the primary entities in this database were Drivers, Visitors, and Groups. The Primary relationships were "belonging" as in visitor belonging to groups and driving as in drivers drive groups. One complication in the original design was that the relationship of drivers and visitors speaking languages was treated more as an entity, so in the design shown below, Language is its own entity with speaks a separate relationship.

Given the Chen Diagram:

As we saw in Visual Paradigm, as you begin to implement the database design from an entity relationship design into a UML design which can lead directly into implementation, there are some issues with many to many relationships. In fact, any many to many relationship by necessity requires the building of a table which represents the relationship (looking now like an entity in the UML diagram below.) VisualParadigm adds in the tables (and foreign keys) when you try to make a many to many relationship. See below:

So Visual Paradigm is doing some of the conversion of an ERD design document to implementation. The keys and foreign keys provide the connection "glue" between the entities.

Database Overview

The schema listed below will enable you to build this database in your own database on db.

1. Drivers(driverId: number, firstName: string, lastName: string, age: number, numSeats: number)
2. DLangs(driverId: number, language: string)
3. Visitors(visitorId: number, groupId: number, firstName: string, lastName: string, age: number)
4. VLangs(visitorId: number, language: string)
5. Groups(groupId: number, country: string)
6. Rides(driverId: number, groupId: number, fromDate: date, toDate: date)
7. Languages(language: string)

Sample data

Drivers: 1, Ahmed Elmagarmid, 25, 5 2, Walid Aref, 27, 13 3, Christopher Clifton, 18, 4 4, Sunil Prabhakar, 22, 7 5, Elisa Bertino, 26, 5 6, Susanne Hambrusch, 23, 3 7, David Eberts, 24, 8 8, Arif Ghafoor, 20, 5 9, Jeff Vitter, 19, 10 DLangs: 1, German 1, English 2, Chinese 3, Arabic 4, English 4, Japanese 4, Russian 5, Spanish 5, English 6, French 7, Japanese 7, Italian 8, Portuguese 9, Spanish 9, English Visitors: 1, 3, Yi-Cheng Tu, 52 2, 4, Hazem Elmeleegy, 35 3, 6, Yuni Xia, 65 4, 7, Hicham Elmongui, 40 5, 5, Mohamed Ali, 66 6, 4, Chris Mayfield, 74 7, 7, Xiaopeng Xiong, 45 8, 3, Ilya Figotin, 62 9, 1, Sarvjeet Singh, 57 10, 2, Mehmet Nergiz, 49 11, 5, Wei Jiang, 38 12, 3, Thanaa Ghanem, 52 13, 2, Murat Kantarcioglu, 64 14, 6, Mohamed Mokbel, 55 15, 7, Mohamed Shehab, 43 16, 1, Mohamed Eltbakh, 70 Groups: 1, Germany 2, England 3, USA 4, China 5, Brazil 6, India 7, Japan VLangs: 1, Arabic 1, Japanese 2, Italian 3, English 3, Chinese 3, French 4, Arabic 5, English 6, Japanese 6, Italian 7, Portuguese 7, Spanish 8, Spanish 8, English 9, Japanese 10, Russian 10, English 11, Spanish 12, German 12, English 13, English 14, French 14, Russian 15, Spanish 16, English Rides: 3, 5, 2/10/2012, 2/13/2012 1, 2, 2/12/2012, 2/14/2012 9, 1, 2/15/2012, 2/15/2012 5, 7, 2/14/2012, 2/18/2012 1, 3, 2/15/2012, 2/16/2012 2, 6, 2/17/2012, 2/20/2012 3, 4, 2/18/2012, 2/19/2012 4, 1, 2/19/2012, 2/19/2012 2, 7, 2/18/2012, 2/23/2012 8, 5, 2/20/2012, 2/22/2012 3, 2, 2/24/2012, 2/26/2012 6, 6, 2/25/2012, 2/26/2012   Languages: German English Chinese Arabic Japanese French Italian Portuguese Spanish Russian

Part 1 - Getting Started

For this lab, you will use the design that you did in Visual Paradigm and implement this design using the DDL and DDM for postgresql.

Log into Postgresql the same way you did for our first lab. You will log in automatically to your eid database.

As you build each query, load them into a plain text file (or build the query in the text file and copy and paste them into the terminal window). You will build a script to create this database if you ever need to create it (like if you hose something in trying to build a query...I speak from experience.) See sample.

Part 2 - Building your database Create, Alter

You should start your Create script with DROP TABLES - see sample. This will enable you to start from scratch if ever you want to rebuild your db. (And you likely will).

The two commands you will be working with are: (Postgresql Reference)

• CREATE TABLE – Syntax: CREATE TABLE tablename (att1 att1type, att2 att2type, …. attn attntype, [PRIMARY KEY (att1)];  You can include a primary key either by adding PRIMARY KEY to the attribute or by having a PRIMARY KEY clause at the end.  So to make table Foo with 2 attributes, one of which is the primary key, you could type, CREATE TABLE Foo (att1 char(5) PRIMARY KEY, (att2 int)) ; OR CREATE TABLE Foo (att1 char(5), att2 int, PRIMARY KEY(att1))  
• ALTER TABLE – Syntax: ALTER TABLE tablename ADD CONSTRAINT constraintName FOREIGN KEY att1 REFERENCES table(attribute);  constraintName is optional.

These commands are part of the DDL or Data Definition Language.

Notice that you can add primary key constraints as you are building the tables. The primary key is the underlined attributes. For composite keys, primary key clauses come at the end of the build. For individual keys you can define them with the attribute.

Part 3 - Populate your database

1. While the sample has a single script, it is often better to separate the DDL and DML scripts into two separate documents.
2. Since you may run this multiple times, you should start your script with a series of deletes to clear the data that may be leftover from prior batch runs.

3. A DELETE FROM tablename; will delete all rows from the tablename table.
4. Then...
5. The INSERT Command lets you insert new data into a table individually.

   To insert data into a table:

   INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99)

6. Create the insert commands to insert the given data into the tables. Since we don't have referential integrity yet, it does not matter in what order you run build your inserts.
7. Test as you are going along. Try inserting one row and then copy and paste the rest into your script.
8. To run the script (assuming you started psql from the directory in which the script is located), type \i scriptname.
9. Check to see that your table is populated by running SELECT * FROM tablename. This displays everything in the given table.

 

 

Updated 02/05//2014 - NLH